--Procedimiento almacenado para calcular totales.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE calcularTotales
    @ID int /*ID de proyectos*/
	AS
		DECLARE @PFAlbrechtT [decimal] (10,2),
		@PFGarmusT [decimal] (10,2),
		@CasperJonesGarmusT [decimal] (10,2),
		@ISBSGGarmusT [decimal] (10,2),
		@DuracionEstimadaGarmusT [decimal] (10,2),
		@ID_Iteracion int,
		@PFGarmus [decimal] (10,2),
		@PFAlbrecht [decimal] (10,2), 
		@CJGarmus [decimal] (10,2),
		@ISBGGarmus [decimal] (10,2) 
				
		DECLARE c CURSOR READ_ONLY
		FOR 
			SELECT Distinct (i.ID),i.PFGarmus, i.PFAlbrecht, i.CJGarmus, i.ISBGGarmus
			FROM Estructuracion1 e join Iteraciones i on (e.IteracionID = i.ID) 
			WHERE e.ProyectoID = @ID
	BEGIN 
		SET @PFAlbrechtT = 0
		SET @PFGarmusT = 0
		SET @CasperJonesGarmusT = 0
		SET @ISBSGGarmusT = 0 
		OPEN c
		FETCH NEXT FROM c
		INTO @ID_Iteracion, @PFGarmus, @PFAlbrecht, @CJGarmus, @ISBGGarmus
		WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @PFAlbrechtT = @PFAlbrechtT + @PFAlbrecht 
				SET @PFGarmusT = @PFGarmusT + @PFGarmus
				SET @CasperJonesGarmusT = @CasperJonesGarmusT + @CJGarmus
				SET @ISBSGGarmusT = @ISBSGGarmusT + @ISBGGarmus
				
				FETCH NEXT FROM c
				INTO @ID_Iteracion, @PFGarmus, @PFAlbrecht, @CJGarmus, @ISBGGarmus
			END
		UPDATE Proyectos
		SET PFGarmus = @PFGarmusT, PFAlbrecht = @PFAlbrechtT, CasperJonesGarmus = @CasperJonesGarmusT, ISBSGGarmus = @ISBSGGarmusT 
		WHERE ID = @ID 
		CLOSE c 
		DEALLOCATE c 
	END
GO




EXECUTE calcularTotales 10
DROP PROCEDURE calcularTotales

Select * FROM Proyectos
Select * FROM Iteraciones
SELECT * FROM Estructuracion1	
